package com.irdstudio.apicenter.console.dm.module;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.stereotype.Component;

import com.irdstudio.apicenter.console.dm.AppFrameModule;
import com.irdstudio.apicenter.frame.core.jdbc.util.ConnectionManager;
import com.irdstudio.apicenter.frame.maker.dm.SModulePageBase;
import com.irdstudio.apicenter.frame.maker.dm.SModuleParams;

/**
 * 模块信息管理
 * 
 * @author jr
 * 
 */
@Component(value = "moduleDao")
public class ModuleDao {

	/**
	 * 查询模块信息 传入参数为空时默认查询全部
	 * 
	 * @param id
	 *            模块编号
	 * @param name
	 *            模块名称
	 * @param pageNowe
	 *            页数
	 * @param pageSiz
	 *            每页数据量
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<ModuleEntity> queryModule(String sql) throws SQLException,
			ClassNotFoundException {
		Connection conn = ConnectionManager.getConnection();
		List list = new ArrayList();
		PreparedStatement ps = null;
		ResultSet rs = null;
		System.out.println("列表查询语句：" + sql);
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				ModuleEntity me = new ModuleEntity();
				me.setModule_id(rs.getString("module_id"));
				me.setModule_name(rs.getString("module_name"));
				me
						.setModule_index_page_id(rs
								.getString("module_index_page_id"));
				me.setLast_modify_date(rs.getString("last_modify_date"));
				list.add(me);
			}

		} catch (Exception e) {
			throw new SQLException("查询模块信息异常!" + e.getMessage());
		} finally {
			close(rs, null, ps);
			conn.close();
		}
		return list;

	}

	/**
	 * 查询某一个模块的详细信息
	 * 
	 * @param id
	 *            模块编号
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public ModuleEntity queryModuleDetail(String id) throws SQLException,
			ClassNotFoundException {
		Connection conn = ConnectionManager.getConnection();
		ModuleEntity me = new ModuleEntity();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "";
		try {
			sql = "select * " + "from s_module_base " + "where module_id= '"
					+ id + "'";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				me.setModule_id(rs.getString("module_id"));
				me.setModule_name(rs.getString("module_name"));
				me
						.setModule_index_page_id(rs
								.getString("module_index_page_id"));
				me.setLong_desc(rs.getString("LONG_DESC"));
			}
		} catch (SQLException e) {
			throw new SQLException("查询模块信息异常!" + e.getMessage());
		} finally {
			conn.close();
			close(rs, null, ps);
		}
		return me;

	}

	/**
	 * 查询某一个模块的详细信息
	 * 
	 * @param id
	 *            模块编号
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<SModuleParams> queryModuleParam(String id) throws SQLException,
			ClassNotFoundException {
		Connection conn = ConnectionManager.getConnection();
		SModuleParams me = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "";
		List list = new ArrayList();
		try {
			sql = "select * " + "from s_module_param " + "where module_id= '"
					+ id + "'";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				me = new SModuleParams();
				me.setModuleId(rs.getString("module_id"));
				me.setParamCode(rs.getString("param_code"));
				me.setParamName(rs.getString("param_name"));
				me.setParamDefaultValue(rs.getString("param_default_value"));
				me.setParamRemarks(rs.getString("param_remarks"));
				list.add(me);
			}
		} catch (SQLException e) {
			throw new SQLException("查询模块信息异常!" + e.getMessage());
		} finally {
			conn.close();
			close(rs, null, ps);
		}
		return list;

	}

	/**
	 * 新增模块
	 * 
	 * @param sModuleParams
	 * 
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public int Addmodel(ModuleEntity moduleEntity, List<SModuleParams> list)
			throws SQLException, ClassNotFoundException {
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		int bl = 0;
		String sql1 = "";
		String sql2 = "";
		try {
			sql1 = "insert into s_module_base(module_id,module_name,module_index_page_id) values ('"
					+ moduleEntity.getModule_id()
					+ "','"
					+ moduleEntity.getModule_name()
					+ "','"
					+ moduleEntity.getModule_index_page_id() + "')";
			sql2 = "insert into s_module_param(module_id,param_code,param_name,param_default_value,param_remarks) values (?,?,?,?,?)";
			ps = conn.prepareStatement(sql1);
			bl = ps.executeUpdate();
			if (bl != 0) {
				SModuleParams sp = null;
				for (int i = 0; i < list.size(); i++) {
					sp = new SModuleParams();
					sp = list.get(i);
					ps = null;
					ps = conn.prepareStatement(sql2);
					ps.setString(1, moduleEntity.getModule_id());
					ps.setString(2, sp.getParamCode());
					ps.setString(3, sp.getParamName());
					ps.setString(4, sp.getParamDefaultValue());
					ps.setString(5, sp.getParamRemarks());
					bl = ps.executeUpdate();

				}

			}

		} catch (SQLException e) {
			throw new SQLException("模块新增信息异常!" + e.getMessage());
		} finally {
			conn.close();
			close(null, null, ps);
		}
		return bl;

	}

	/**
	 * 删除模块
	 * 
	 * @param sModuleParams
	 * 
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public int deletemodel(String id) throws SQLException,
			ClassNotFoundException {
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		int bl = 0;
		String sql1 = "";
		try {
			sql1 = "delete from s_module_base where module_id='" + id + "'";
			ps = conn.prepareStatement(sql1);
			bl = ps.executeUpdate();

		} catch (SQLException e) {
			throw new SQLException("模块删除信息异常!" + e.getMessage());
		} finally {
			conn.close();
			close(null, null, ps);
		}
		return bl;

	}

	/**
	 * 更新参数
	 * 
	 * @param sModuleParams
	 * 
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public int updatemodelparam(String id, List<SModuleParams> list)
			throws SQLException, ClassNotFoundException {
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		int bl = 0;
		Boolean b = false;
		String sql2 = "";
		String sql3 = "";
		try {
			sql2 = "delete from s_module_param where module_id ='" + id + "' ";

			ps = conn.prepareStatement(sql2);
			bl = ps.executeUpdate();

			SModuleParams sp = null;
			for (int i = 0; i < list.size(); i++) {
				sp = new SModuleParams();
				sp = list.get(i);
				ps = null;
				sql3 = "insert into s_module_param(module_id,param_code,param_name,param_default_value,param_remarks) values ('"
						+ id
						+ "','"
						+ sp.getParamCode()
						+ "','"
						+ sp.getParamName()
						+ "','"
						+ sp.getParamDefaultValue()
						+ "','" + sp.getParamRemarks() + "')";
				ps = conn.prepareStatement(sql3);
				b = ps.execute();

			}
			if (b == false) {
				bl = 1;
			} else {
				bl = 0;
			}
		} catch (SQLException e) {
			throw new SQLException("模块更新信息异常!" + e.getMessage());
		} finally {
			conn.close();
			close(null, null, ps);
		}
		return bl;

	}

	/**
	 * 更新参数
	 * 
	 * @param sModuleParams
	 * 
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public int updatemodel(ModuleEntity moduleEntity) throws SQLException,
			ClassNotFoundException {
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		int bl = 0;
		String sql1 = "";
		try {
			sql1 = "update s_module_base set module_name=?,module_index_page_id=? long_desc=? where module_id='"
					+ moduleEntity.getModule_id() + "'";
			ps = conn.prepareStatement(sql1);
			ps.setString(1, moduleEntity.getModule_name());
			ps.setString(2, moduleEntity.getModule_index_page_id());
			ps.setString(3, moduleEntity.getLong_desc());
			bl = ps.executeUpdate();

		} catch (SQLException e) {
			throw new SQLException("模块更新信息异常!" + e.getMessage());
		} finally {
			conn.close();
			close(null, null, ps);
		}
		return bl;

	}

	/**
	 * 判断模块代码是否存在
	 * 
	 * @param sModuleParams
	 * 
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public int findid(String id) throws SQLException {
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int diflag = 0;
		try {
			ps = conn
					.prepareStatement("SELECT count(1) as APP_ID  FROM s_module_param WHERE param_code=?");
			ps.setString(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				diflag = Integer.parseInt(rs.getString("APP_ID"));
			}
		} catch (SQLException e) {
			throw new SQLException("模块新增信息异常!" + e.getMessage());
		} finally {
			conn.close();
			close(rs, null, ps);
		}
		return diflag;

	}

	public List<SModulePageBase> queryPageBase(String id) throws SQLException,
			ClassNotFoundException {
		Connection conn = ConnectionManager.getConnection();
		List<SModulePageBase> l = new ArrayList<SModulePageBase>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from s_module_page a,s_page_base b where a.module_id='"
				+ id + "' and a.page_id=b.page_id";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				SModulePageBase me = new SModulePageBase();
				me.setPageId(rs.getString("page_id"));
				me.setPageName(rs.getString("page_name"));
				me.setPageMakeMode(rs.getString("page_make_mode"));
				me.setTemplateId(rs.getString("template_id"));
				me.setLastModifyDate(rs.getString("last_modify_date"));
				l.add(me);

			}
		} catch (SQLException e) {
			throw new SQLException("查询模块信息异常!" + e.getMessage());
		} finally {
			conn.close();
			close(rs, null, ps);
		}
		return l;

	}

	public int updateModulePage(String id, List<SModulePageBase> list)
			throws SQLException, ClassNotFoundException {
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		int bl = 0;
		Boolean b = false;
		String sql2 = "";
		String sql3 = "";
		try {
			sql2 = "delete from s_module_page where module_id ='" + id + "' ";

			ps = conn.prepareStatement(sql2);
			bl = ps.executeUpdate();

			for (int i = 0; i < list.size(); i++) {
				SModulePageBase sp = new SModulePageBase();
				sp = list.get(i);
				ps = null;
				sql3 = "insert into s_module_page(module_id,page_id) values ('"
						+ id + "','" + sp.getPageId() + "')";
				ps = conn.prepareStatement(sql3);
				b = ps.execute();

			}
			if (b == false) {
				bl = 1;
			} else {
				bl = 0;
			}
		} catch (SQLException e) {
			throw new SQLException("模块更新信息异常!" + e.getMessage());
		} finally {
			conn.close();
			close(null, null, ps);
		}
		return bl;

	}

	public List<SModulePageBase> modulepagelist() throws SQLException,
			ClassNotFoundException {
		Connection conn = ConnectionManager.getConnection();
		List l = new ArrayList();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select b.PAGE_ID,b.PAGE_name, b.PAGE_MAKE_MODE,b.last_modify_date  from  s_page_base b ";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				SModulePageBase me = new SModulePageBase();
				me.setPageId(rs.getString("page_id"));
				me.setPageName(rs.getString("page_name"));
				me.setPageMakeMode(rs.getString("page_make_mode"));
				me.setLastModifyDate(rs.getString("last_modify_date"));
				l.add(me);

			}
		} catch (SQLException e) {
			throw new SQLException("查询模块信息异常!" + e.getMessage());
		} finally {
			conn.close();
			close(rs, null, ps);
		}
		return l;

	}
	
	public int insertModuleBase(ModuleEntity module) throws SQLException {
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		int flag = -1;
		try {
			ps = conn
					.prepareStatement("insert into s_module_base(module_id,module_name,module_index_page_id,last_modify_date) values (?,?,?,?)");
			ps.setString(1, module.getModule_id());
			ps.setString(2, module.getModule_name());
			ps.setString(3, module.getModule_index_page_id());
			ps.setString(4, new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
			flag = ps.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("insert into s_module_base is Wrong!"
					+ e.getMessage());
		} finally {
			conn.close();
			close(null, null, ps);
		}
		return flag;
	}
	
	/**
	 * 关闭资源
	 * 
	 * @param theRs
	 * @param theStmt
	 * @param thePs
	 */
	protected void close(ResultSet theRs, Statement theStmt,
			PreparedStatement thePs) {
		try {
			if (theRs != null)
				theRs.close();
			if (theStmt != null)
				theStmt.close();
			if (thePs != null)
				thePs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
